package com.canin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.canin.model.Size;

public class SizeDAO
{
	private Connection conn = null;

	public SizeDAO(Connection conn)
	{
		this.conn = conn;
	}

	public ArrayList<Size> getAllSize() throws Exception
	{
		ArrayList<Size> sizeList = new ArrayList<Size>();

		String sql = "select * from standard_size";
		PreparedStatement ps = conn.prepareStatement(sql);

		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			Size size = new Size();
			
			size.setId( rs.getInt( "ID" ) );
			size.setBreedSize( rs.getString( "breed_size" ) );
			size.setStartAge( rs.getInt( "start_age" ) );
			size.setEndAge( rs.getInt( "end_age" ) );
			size.setStandardSize( rs.getString( "standard_size" ) );
			
			sizeList.add( size );
		}

		rs.close();
		ps.close();

		return sizeList;
	}
	
	public Size getSizeDetail( String breedSize, String standardSize ) throws SQLException
	{
		Size size = new Size();

		String sql = "select * from standard_size where breed_size=? and standard_size=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, breedSize);
		ps.setString(2, standardSize);

		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			size.setId( rs.getInt( "ID" ) );
			size.setBreedSize( rs.getString( "breed_size" ) );
			size.setStartAge( rs.getInt( "start_age" ) );
			size.setEndAge( rs.getInt( "end_age" ) );
			size.setStandardSize( rs.getString( "standard_size" ) );
		}

		rs.close();
		ps.close();
		
		return size;
	}
	
	public boolean updateSizeDetail( int id, int startAge, int endAge ) throws SQLException
	{
		String sql = "UPDATE standard_size SET start_age=?, end_age=? " +
				"WHERE id=?";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, startAge);
		ps.setInt(2, endAge);
		ps.setInt(3, id);

		ps.executeUpdate();
		ps.close();
		
		conn.close();
		
		return true;
	}

}
